package com.epam.iunna.bookshop.model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.epam.iunna.bookshop.model.service.ItemBean;
import com.epam.iunna.bookshop.model.util.ConnectionsPool;
import com.epam.iunna.bookshop.model.util.DBUtils;

/**
 * @author Iunna
 * 
 */
public class ItemDao {

    public Item getByAuthor(String author) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where author=?");
                statement.setString(1, author);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByCover(String cover) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where cover=?");
                statement.setString(1, cover);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByDescription(String description) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where description=?");
                statement.setString(1, description);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByGenre(String genre) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where genre=?");
                statement.setString(1, genre);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getById(Long id) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where id=?");
                statement.setLong(1, id);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByLang(String lang) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where lang=?");
                statement.setString(1, lang);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByName(String name) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where name=?");
                statement.setString(1, name);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByPrice(Long price) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where price=?");
                statement.setLong(1, price);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByPublisher(String publisher) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where publisher=?");
                statement.setString(1, publisher);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByQuantity(Long quantity) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where quantity_avaliable=?");
                statement.setLong(1, quantity);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByRating(Long rating) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where rating=?");
                statement.setLong(1, rating);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByShetsNum(Long sheetsNum) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where sheetsNum=?");
                statement.setLong(1, sheetsNum);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getBySold(Long sold) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where sold=?");
                statement.setLong(1, sold);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public Item getByYear(Long year) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;
        Item item = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select * from item where year=?");
                statement.setLong(1, year);
                statement.executeQuery();
                result = statement.getResultSet();
                if (result.next()) {
                    return getItemPojo(result);
                }
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return item;
    }

    public List<Item> getAll() throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        Statement statement = null;

        List<Item> itemList = new LinkedList<Item>();

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection.createStatement();
                statement
                        .executeQuery("select * from item where deleted = false");
                result = statement.getResultSet();
                while (result.next()) {
                    itemList.add(getItemPojo(result));
                }
                return itemList;
            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return null;
    }

    public void addItem(Item itemPojo) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("insert into item (author,cover,description,genre,id,lang,name,price,publisher,rating,sheetsNum,sold,year,quantity_avaliable) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                statement.setString(1, itemPojo.getAuthor());
                statement.setString(2, itemPojo.getCover());
                statement.setString(3, itemPojo.getDescription());
                statement.setString(4, itemPojo.getGenre());
                statement.setLong(5, itemPojo.getId());
                statement.setString(6, itemPojo.getLang());
                statement.setString(7, itemPojo.getName());
                statement.setBigDecimal(8, itemPojo.getPrice());
                statement.setString(9, itemPojo.getPublisher());
                statement.setLong(10, itemPojo.getRating());
                statement.setLong(11, itemPojo.getSheetsNum());
                statement.setLong(12, itemPojo.getSold());
                statement.setLong(13, itemPojo.getYear());
                statement.setLong(14, itemPojo.getQuantityAvaliable());
                statement.executeUpdate();
            }
        } finally {
            closeConnection(connection, statement, null);
        }
    }

    public void removeItem(Long itemId) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.

                statement = connection
                        .prepareStatement("update item set deleted = true where id = ?");

                statement.setLong(1, itemId);
                statement.executeUpdate();
            }
        } finally {
            closeConnection(connection, statement, null);
        }
    }

    public List<ItemBean> getListByOrderID(Long orderID) throws SQLException {
        Connection connection = null;
        ResultSet result = null;
        PreparedStatement statement = null;

        List<ItemBean> itemList = new LinkedList<ItemBean>();
        try {

            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("select it.*,oit.quantity from orderitem oit inner join item it on it.id = oit.itemid and orderid=? ");
                statement.setLong(1, orderID);
                statement.executeQuery();
                result = statement.getResultSet();

                do {
                    result.next();
                    Item item = getItemPojo(result);
                    ItemBean itemBean = new ItemBean(item,
                            result.getLong("quantity"));
                    itemList.add(itemBean);
                } while (!result.isLast());

            }
        } finally {
            closeConnection(connection, statement, result);
        }
        return itemList;
    }

    public void updateQuantityAvaliable(Item item) throws SQLException {
        Connection connection = null;
        PreparedStatement statement = null;

        try {
            connection = ConnectionsPool.getInstance().getConnection();

            // Do we have a connection?
            if (!connection.isClosed()) {
                // We have a MySQL Connection.
                statement = connection
                        .prepareStatement("update item set quantity_avaliable=? where id=?");
                statement.setLong(1, item.getQuantityAvaliable());
                statement.setLong(2, item.getId());

                statement.executeUpdate();

            } else {
                throw new SQLException("Cannot obtain db connection.");
            }
        } finally {
            DBUtils.closeConnection(connection, statement, null);
        }
    }

    public static void main(String[] args) throws SQLException {
        UserDao userDao = new UserDao();
        User user = userDao.getById(1L);
        System.out.println(user.toString());
        // user = userDao.getByLogin("");
    }

    /**
     * @param connection
     *            connection to db
     * @param statement
     *            statement to db * @param result result set from db
     * @throws SQLException
     */
    private void closeConnection(Connection connection, Statement statement,
            ResultSet result) throws SQLException {
        try {
            if (result != null) {
                result.close();// close result set
            }
        } finally {
            try {
                if (statement != null) {
                    statement.close();// close statement
                }
            } finally {
                // If we don't have a error, close the connection!
                if (connection != null) {
                    connection.close();
                }
            }
        }
    }

    /**
     * @param userRecord
     *            user record from db
     * @return User POJO
     * @throws SQLException
     */
    private Item getItemPojo(ResultSet itemRecord) throws SQLException {
        Item item = new Item();
        item = new Item();
        item.setAuthor(itemRecord.getString("author"));
        item.setCover(itemRecord.getString("cover"));
        item.setDescription(itemRecord.getString("description"));
        item.setGenre(itemRecord.getString("genre"));
        item.setId(itemRecord.getLong("id"));
        item.setLang(itemRecord.getString("lang"));
        item.setName(itemRecord.getString("name"));
        item.setPrice(itemRecord.getBigDecimal("price"));
        item.setPublisher(itemRecord.getString("publisher"));
        item.setRating(itemRecord.getLong("rating"));
        item.setSheetsNum(itemRecord.getLong("sheetsNum"));
        item.setSold(itemRecord.getLong("sold"));
        item.setYear(itemRecord.getLong("year"));
        item.setQuantityAvaliable(itemRecord.getLong("quantity_avaliable"));
        return item;
    }
}